const express = require('express');
const router = express.Router();
const mysql = require('mysql');
const { dbInfo } = require('../config/config.default')
//创建连接
const connection = mysql.createConnection(dbInfo);
//执行创建连接 
connection.connect();


// 查询所有的铁路信息
const queryRailwayAllInfoSql = 'SELECT DISTINCT * FROM railway';
router.get('/queryRailwayAllInfo', async (req, res, next) => {
    connection.query(queryRailwayAllInfoSql, function (err, result) {
        if (err) {
            res.json({
                error: err.message
            })
            console.log('[SELECT ERROR] -', err.message);
            return;
        }
        res.send(result);
    })
})

// 查询不同类型列车数量
const queryRailType = 'SELECT distinct checi FROM railway';
router.get('/queryRailType', async (req, res, next) => {
    connection.query(queryRailType, function (err, result) {
        if (err) {
            res.json({
                error: err.message
            })
            console.log('[SELECT ERROR] -', err.message);
            return;
        }
        res.send(result);
    })
})

// 查询不同等级座位的平均价格
const queryLeveLAvePriceSql = 'select first_ticket_price,second_ticket_price,noseat_ticket_price from railway';
router.get('/queryLeveLAvePrice', async (req, res, next) => {
    connection.query(queryLeveLAvePriceSql, function (err, result) {
        if (err) {
            res.json({
                error: err.message
            })
            console.log('[SELECT ERROR] -', err.message);
            return;
        }
        res.send(result);
    })
})

const sql = "select d_time,first_ticket_price,second_ticket_price,noseat_ticket_price from railway where first_ticket_price !='None' And second_ticket_price!='None' and noseat_ticket_price !='None'"
router.get('/queryLineChart', async (req, res, next) => {
    connection.query(sql, function (err, result) {
        if (err) {
            res.json({
                error: err.message
            })
            console.log('[SELECT ERROR] -', err.message);
            return;
        }
        res.send(result);
    })
})


module.exports = router;
